package approve.dao;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import models.util.MySqlDataAccessHelper;

import approve.model.Approvedoc;

public class MyDocumentDAO {
	public static List<Approvedoc> getAllMyDoc(String databasename, String idem, String flagsort,int statusvalue){
		List<Approvedoc> lst = new ArrayList<Approvedoc>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		CallableStatement cs = null;
		ResultSet rs = null;
		String sql = "call android_getAllMyDoc(?,?,?)";
		try{
			helper.open(databasename);
			cs = helper.getConnection().prepareCall(sql);
			cs.setString(1, idem);
			cs.setInt(2,statusvalue);
			cs.setString(3,flagsort);
			rs = cs.executeQuery();
			while(rs.next()){
				lst.add(new Approvedoc(rs.getString("IDApproveDoc"), rs.getString("Tile"), rs.getString("Content"),
						rs.getString("IDEmployee"), rs.getString("IDGroupApprove"), rs.getTimestamp("Timecreate"), 
						rs.getInt("IDStatus"),rs.getInt("IDTypeDoc"), rs.getTimestamp("TimeApprove"), rs.getString("comment"),
						rs.getString("Description"), rs.getString("purpose"), rs.getString("EmployeePerforment"),
						rs.getTimestamp("TripDate"), rs.getString("TotalTime"), rs.getString("Location"),
						rs.getString("Content2"),rs.getInt("isView")));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				if(helper.getConnection() != null){
					helper.close();
				}
				if (cs != null) {
					cs.close();   	
	            }
				if(rs != null){
					rs.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}   
		}
		return lst;
	}

	public static List<Approvedoc> getAllMyDocByTimeCreate(String databasename, String idem, String flagsort,int statusvalue){
		List<Approvedoc> lst = new ArrayList<Approvedoc>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		CallableStatement cs = null;
		ResultSet rs = null;
		String sql = "call android_getAllMyDocByTimeCreate(?,?,?)";
		try{
			helper.open(databasename);
			cs = helper.getConnection().prepareCall(sql);
			cs.setString(1, idem);
			cs.setInt(2,statusvalue);
			cs.setString(3,flagsort);
			rs = cs.executeQuery();
			while(rs.next()){
				lst.add(new Approvedoc(rs.getString("IDApproveDoc"), rs.getString("Tile"), rs.getString("Content"), rs.getString("IDEmployee"), rs.getString("IDGroupApprove"), rs.getTimestamp("Timecreate"), rs.getInt("IDStatus"),rs.getInt("IDTypeDoc"), rs.getTimestamp("TimeApprove"), rs.getString("comment"), rs.getString("Description"), rs.getString("purpose"), rs.getString("EmployeePerforment"), rs.getTimestamp("TripDate"), rs.getString("TotalTime"), rs.getString("Location"), rs.getString("Content2")));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				if(helper.getConnection() != null){
					helper.close();
				}
				if (cs != null) {
					cs.close();   	
	            }
				if(rs != null){
					rs.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}  
		}
		return lst;
	}
	
	public static List<Approvedoc> getAllMyDocMeeting(String databasename, String idem, String flagsort){
		List<Approvedoc> lst = new ArrayList<Approvedoc>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		CallableStatement cs = null;
		ResultSet rs = null;
		String sql = "call android_getAllMyDocMeeting(?,?)";
		try{
			helper.open(databasename);
			cs = helper.getConnection().prepareCall(sql);
			cs.setString(1, idem);
			cs.setString(2,flagsort);
			rs = cs.executeQuery();
			while(rs.next()){
				lst.add(new Approvedoc(rs.getString("IDApproveDoc"), rs.getString("Tile"), rs.getString("Content"), 
						rs.getString("IDEmployee"), rs.getString("IDGroupApprove"), rs.getTimestamp("Timecreate"),
						rs.getInt("IDStatus"),rs.getInt("IDTypeDoc"), rs.getTimestamp("TimeApprove"), 
						rs.getString("comment"), rs.getString("Description"), rs.getString("purpose"),
						rs.getString("EmployeePerforment"), rs.getTimestamp("TripDate"), rs.getString("TotalTime"),
						rs.getString("Location"), rs.getString("Content2")));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				if(helper.getConnection() != null){
					helper.close();
				}
				if (cs != null) {
					cs.close();   	
	            }
				if(rs != null){
					rs.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}   
		}
		return lst;
	}
	
	public static List<Approvedoc> getAllMySaveDoc(String databasename, String idem, String flagsort){
		List<Approvedoc> lst = new ArrayList<Approvedoc>();
		MySqlDataAccessHelper helper = new MySqlDataAccessHelper();
		CallableStatement cs = null;
		ResultSet rs = null;
		String sql = "call android_getAllMySaveDoc(?,?)";
		try{
			helper.open(databasename);
			cs = helper.getConnection().prepareCall(sql);
			cs.setString(1, idem);
			cs.setString(2,flagsort);
			rs = cs.executeQuery();
			while(rs.next()){
				lst.add(new Approvedoc(rs.getString("IDApproveDoc"), rs.getString("Tile"), rs.getString("Content"), rs.getString("IDEmployee"), rs.getString("IDGroupApprove"), rs.getTimestamp("Timecreate"), rs.getInt("IDStatus"),rs.getInt("IDTypeDoc"), rs.getTimestamp("TimeApprove"), rs.getString("comment"), rs.getString("Description"), rs.getString("purpose"), rs.getString("EmployeePerforment"), rs.getTimestamp("TripDate"), rs.getString("TotalTime"), rs.getString("Location"), rs.getString("Content2")));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try {
				if(helper.getConnection() != null){
					helper.close();
				}
				if (cs != null) {
					cs.close();   	
	            }
				if(rs != null){
					rs.close();
				}
			} catch (Exception e) {
				e.printStackTrace();
			}   
		}
		return lst;
	}
}
